UseCases/Scaling/Demo 3 - System Monitoring Queries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "injured-kennedy",
   "metadata": {},
   "source": [
    "<header style=\"padding:10px;background:#f9f9f9;border-top:3px solid #00b2b1\"><img id=\"Teradata-logo\" src=\"https://www.teradata.com/Teradata/Images/Rebrand/Teradata_logo-two_color.png\" alt=\"Teradata\" width=\"220\" align=\"right\" />\n",
    "\n",
    "<p style = 'font-size:28px;font-family:Arial;color:#E37C4D'><b>VantageCloud Lake Systems Scaling and Monitoring</b></p>\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "signal-suffering",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "<b style = 'font-size:24px;font-family:Arial;color:#E37C4D'>Demo 3 - VantageCloud Lake Systems and User Monitoring Deep-Dive</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>This notebook will illustrate various example queries using Python and SQL to monitor system health, user sessions, and historical events.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>The demonstration consists of short vignettes that illustrate some basic query patterns.</p>\n",
    "<ol style = 'font-size:16px;font-family:Arial'>\n",
    "    <li><b style = 'color:#00b2b1'>Connect to the VantageCloud Lake System</b> - Connect as a user with access to the metrics service and performance monitoring functions</li>\n",
    "    <li><b style = 'color:#00b2b1'>Current Resource Utilization</b> - The current system utilization</li>\n",
    "    <li><b style = 'color:#00b2b1'>Historic Resource Utilization</b> - Queries showing how to query historical resource usage data</li>\n",
    "    <li><b style = 'color:#00b2b1'>Cluster Events</b> - Queries to analyze what compute resources were available when</li>\n",
    "    <li><b style = 'color:#00b2b1'>Active User and Session Monitoring</b> - For active sessions, queries that monitor users and SQL steps and text.  This requires a running workload provided in Demo 1</li>\n",
    "    <li><b style = 'color:#00b2b1'>Query Logging</b> - Database Query Logs</li>\n",
    "    </ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "furnished-playback",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#E37C4D'>Python Package Imports</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>\n",
    "Import the required packages including teradata client packages (teradataml), as well as packages for data management and display control.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "approved-maple",
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "import json\n",
    "from teradataml import *\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "from IPython.display import display\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "numeric-system",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#E37C4D'>Connect to Vantage</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>Before performing any operations in Vantage, we need to connect to the system.  The below code will read in a variables file (vars.json - this has been used in prior environment setup and data engineering examples) and will connect to Vantage with this information.  The Vantage connection is referred to as a \"Context\" - a common python-rdbms connection architecture.</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aging-aaron",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a local dictionary of environment-specific variables\n",
    "\n",
    "# load vars json\n",
    "with open('../../vars.json', 'r') as f:\n",
    "    session_vars = json.load(f)\n",
    "\n",
    "# Use the \"data_engineer\" and Business compute group from the base setup\n",
    "host = session_vars['environment']['host']\n",
    "username = session_vars['hierarchy']['users']['business_users'][0]['username']\n",
    "password = session_vars['hierarchy']['users']['business_users'][0]['password']\n",
    "\n",
    "eng = create_context(host = host, username = username, password = password)\n",
    "\n",
    "# confirm connection\n",
    "print(eng)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "constitutional-public",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:24px;font-family:Arial;color:#E37C4D'><b>Compute Cluster Information</b></p>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#E37C4D'>All Compute Profiles</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>Get all configured Compute Profiles in all Compute Groups, and list their status and internal identifiers.  Note that Compute Profiles that have been configured with more than one instance will have those instances listed here regardless of whether they are active or not.</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "residential-berkeley",
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = '''\n",
    "/* gets compute cluster name, internal identifiers, and status */\n",
    "select\n",
    "    g.ComputeGroupName,\n",
    "    p.ComputeProfileName,\n",
    "    g.ComputeGroupUniqName,\n",
    "    p.ComputeProfileUniqName,\n",
    "    s.InstanceName, \n",
    "    s.CurrentState,\n",
    "    s.LastReqState\n",
    "from DBC.ComputeGroupsV g, DBC.ComputeProfilesV p, DBC.ComputeStatusV s\n",
    "where\n",
    "    g.ComputeGroupName=p.ComputeGroupName and\n",
    "    p.ComputeProfileName=s.ComputeProfileName\n",
    "ORDER BY 1,6\n",
    "    ;'''\n",
    "\n",
    "pd.read_sql(qry, eng)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "immediate-segment",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#E37C4D'>All Active Profiles</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>This is helpful to monitor just the active Compute Profile instances.  During times of scaling - or - if the Profile has been configured to have multiple active instances, each active instance will have a unique InstanceName.</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "greatest-millennium",
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = '''\n",
    "/* gets compute cluster name, internal identifiers */\n",
    "select\n",
    "    g.ComputeGroupName,\n",
    "    p.ComputeProfileName,\n",
    "    s.ComputeInstanceType,\n",
    "    g.ComputeGroupUniqName,\n",
    "    p.ComputeProfileUniqName,\n",
    "    s.InstanceName \n",
    "from DBC.ComputeGroupsV g, DBC.ComputeProfilesV p, DBC.ComputeStatusV s\n",
    "where\n",
    "    g.ComputeGroupName=p.ComputeGroupName and\n",
    "    p.ComputeProfileName=s.ComputeProfileName\n",
    "    and s.CurrentState='Active'\n",
    "    ;'''\n",
    "pd.read_sql(qry, eng)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "orange-tract",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:24px;font-family:Arial;color:#E37C4D'><b>Current System Performance</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'><a href = 'https://docs.teradata.com/r/Teradata-VantageCloud-Lake/Monitoring-Performance-and-Resource-Usage/Performance-and-Query-Monitoring/Single-Operational-View-APIs'>Single Operational View APIs</a> provide the ability to view current system and query performance.  See the documentation for the full set of APIs and parameters. Some examples follow</p> \n",
    "\n",
    "<hr>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#E37C4D'>CPU Utilization for Compute Groups</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>Use the MonitorPhysicalResourceSV API and the configuration information to view CPU utilization for the each Compute Profile Instance.</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "greatest-appeal",
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = '''\n",
    "/* overall CPU for a compute group, associated profile, and instance*/\n",
    "\n",
    "SELECT s.InstanceName, p.ComputeProfileName, g.ComputeGroupName,  AVERAGE(dt.CPUUse) as CPUUse FROM\n",
    "     syslib.MonitorPhysicalResourceSV(USING details('1')) dt, DBC.ComputeGroupsV g, DBC.ComputeProfilesV p, DBC.ComputeStatusV s\n",
    "\n",
    "WHERE dt.\"Group\" IS NOT NULL AND\n",
    "         s.CurrentState='Active' AND\n",
    "         dt.\"Group\" = g.ComputeGroupUniqName AND\n",
    "         g.ComputeGroupName=p.ComputeGroupName AND\n",
    "         p.ComputeProfileName=s.ComputeProfileName\n",
    "GROUP BY 1,2,3'''\n",
    "\n",
    "pd.read_sql(qry, eng)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "competent-tumor",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#E37C4D'>All Physical Resource Utilization for Primary Groups</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>Use the MonitorPhysicalResourceSV API alone to view \"primary cluster\" resources.</p> \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "packed-helicopter",
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = '''\n",
    "SELECT  * FROM\n",
    "     syslib.MonitorPhysicalResourceSV(USING details('1')) dt\n",
    "WHERE \"Type\" = 'primary cluster'\n",
    "ORDER BY 1'''\n",
    "\n",
    "pd.read_sql(qry, eng)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "affected-premium",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#E37C4D'>Select Physical Resource Utilization for all system components</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>Use the MonitorPhysicalResourceSV API alone to view all components, select resource utilization.</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bacterial-amplifier",
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = '''\n",
    "/* Per-node, per-instance, high level information */\n",
    "\n",
    "SELECT dt.\"ProcID\" as \"ProcID\",\n",
    "    dt.\"Type\" as \"Type\",\n",
    "    dt.\"Status\" as \"Status\",\n",
    "    dt.\"AmpCount\" as \"AmpCount\",\n",
    "    dt.\"Group\" as \"Group\", \n",
    "    dt.\"Name\" as \"Name\",\n",
    "    dt.CPUUse as CPUUse \n",
    "\n",
    "    FROM\n",
    "     syslib.MonitorPhysicalResourceSV(USING details('1')) AS dt;\n",
    "     '''\n",
    "\n",
    "pd.read_sql(qry, eng)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "champion-barbados",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#E37C4D'>Virtual Resource Utilization</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>Use the MonitorVirtualResource API alone to view Virtual Resource utilization on the Primary Cluster.</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "centered-classic",
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = '''\n",
    "/* Virtual Resources - doesn't show Compute Clusters */\n",
    "select * from syslib.MonitorVirtualResource(USING details('1')) as x order by 1; \n",
    "'''\n",
    "\n",
    "pd.read_sql(qry, eng)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ranking-michigan",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:24px;font-family:Arial;color:#E37C4D'><b>Historic System Utilization</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'><a href = 'https://docs.teradata.com/r/Teradata-VantageCloud-Lake/Monitoring-Performance-and-Resource-Usage/Performance-and-Query-Monitoring/Performance-Trends-and-History/Monitoring-Resource-Usage'>ResUsage</a>\n",
    "<br>\n",
    "Each resource usage foreign table represents system usage data from a specific perspective collected during the logging period. The logging period is a system defined interval of time during which usage data is accumulated (specified in the NominalSecs column in each table). At the end of the logging interval, usage data is written to each of the resource usage foreign tables. Views have been created that are named similarly to the foreign tables in your unmanaged object storage.</p>\n",
    "\n",
    "<hr>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#E37C4D'>CPU Utilization</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>If you need detailed statistics for each CPU within the system, use this view to evaluate parallelism of CPUs within a node.  Join to DBC tables to get the Instance, Profile, and Group for each component, and calculate percentages.</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ethical-durham",
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = '''\n",
    "SELECT TOP 5 *\n",
    "    FROM\n",
    "    TD_METRIC_SVC.resusagescpuV;\n",
    "     '''\n",
    "display(pd.read_sql(qry, eng))\n",
    "\n",
    "qry = '''\n",
    "SELECT TOP 10 \n",
    "    TO_TIMESTAMP(rs.TheTimestamp) TheTimestamp, \n",
    "    rn.ComputeGroupName, \n",
    "    rn.ComputeProfileName, \n",
    "    rn.InstanceName,\n",
    "    rs.cpuid,\n",
    "    rs.CentiSecs, \n",
    "    rs.CPUIdle/rs.CentiSecs pctIdle, \n",
    "    rs.CPUIoWait/rs.CentiSecs pctIoWait, \n",
    "    rs.CPUUServ/rs.CentiSecs pctUserv, \n",
    "    rs.CPUUexec/rs.CentiSecs pctUexec\n",
    "\n",
    "FROM TD_METRIC_SVC.resusagescpuV rs\n",
    "\n",
    "JOIN (SELECT pr.\"Id\", pr.\"Group\", pr.\"ProcID\", g.ComputeGroupName, p.ComputeProfileName, s.InstanceName\n",
    "            FROM syslib.MonitorPhysicalResourceSV(USING details('1')) pr, DBC.ComputeStatusV s, DBC.ComputeProfilesV p, DBC.ComputeGroupsV g\n",
    "            WHERE pr.\"Id\" LIKE '%' || s.InstanceName || '%' AND\n",
    "                 g.ComputeGroupName=p.ComputeGroupName and\n",
    "                 p.ComputeProfileName=s.ComputeProfileName) rn\n",
    "ON rn.ID = rs.path_component_id\n",
    "'''\n",
    "\n",
    "display(pd.read_sql(qry, eng))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "republican-plaza",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:24px;font-family:Arial;color:#E37C4D'><b>Historic Cluster Events</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'><a href = 'https://docs.teradata.com/r/Teradata-VantageCloud-Lake/Monitoring-Performance-and-Resource-Usage/Performance-and-Query-Monitoring/Performance-Trends-and-History/Monitoring-Compute-Cluster-Events'>Monitoring Compute Cluster Events</a>\n",
    "<br>\n",
    "Compute clusters are elastic compute resources that can be created, started, stopped, hibernated and released dynamically to provide the necessary compute resources when needed, and removed when no longer needed. To better understand the behavior of these compute resources, you can leverage the views and underlying tables to gain insight into the state of your compute clusters and the events that changed a compute cluster state.</p>\n",
    "\n",
    "<hr>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#E37C4D'>Compute Profile History</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>The ComputeProfileHistoryV view can be used to:</p>\n",
    "<ul style = 'font-size:16px;font-family:Arial'>\n",
    "<li>Determine when a compute cluster autoscaled up or down.\n",
    "<li>Identify events that bring a compute cluster online or offline.\n",
    "<li>Validate the identify overlap between different compute profiles in a compute group.\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "unusual-facility",
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = '''\n",
    "SELECT TOP 20 *\n",
    "    FROM\n",
    "    td_metric_svc.ComputeProfileHistoryV\n",
    "    ORDER BY EventTime DESC;\n",
    "     '''\n",
    "display(pd.read_sql(qry, eng))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "disabled-shape",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#E37C4D'>Compute Profile History Events</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>The ComputeProfileHistoryEventsV view can be used to:</p>\n",
    "<ul style = 'font-size:16px;font-family:Arial'>\n",
    "<li>Compute the amount of time during a month when compute clusters were available.\n",
    "<li>Identify time periods when compute clusters were hibernated.\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "choice-transformation",
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = '''\n",
    "SELECT TOP 10 *\n",
    "    FROM\n",
    "    td_metric_svc.ComputeProfileHistoryEventsV\n",
    "    ORDER BY StartTime DESC;\n",
    "     '''\n",
    "display(pd.read_sql(qry, eng))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "regular-scanner",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:24px;font-family:Arial;color:#E37C4D'><b>Active User And Query Monitoring</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'><a href = 'https://docs.teradata.com/r/Teradata-VantageCloud-Lake/Monitoring-Performance-and-Resource-Usage/Performance-and-Query-Monitoring/Single-Operational-View-APIs'>Single Operational View APIs</a> provide the ability to view current system and query performance.  See the documentation for the full set of APIs and parameters. Some examples follow</p>\n",
    "\n",
    "<hr>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#E37C4D'>Monitoring Sessions</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>Use this single operational view API for session or request resource usage statistics across VantageCloud Lake nodes. This API can return session information across VantageCloud Lake topology (primary clusters and compute clusters). This API can also be used to trace query session to a compute node.</p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial'>\n",
    "    <li>First, show all sessions</li>\n",
    "    <li>Next, show all sessions with active queries, connected to the primary cluster</li>\n",
    "    </ol>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "significant-guatemala",
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = '''\n",
    "SELECT TOP 5 *\n",
    "  FROM MONITORSESSIONSV(   \n",
    " USING HOSTID('-1')\n",
    "       USERNAME('*')\n",
    "       SESSIONID('0')\n",
    "       ) AS DT;\n",
    "       '''\n",
    "display(pd.read_sql(qry, eng))\n",
    "\n",
    "qry = '''\n",
    "SELECT UserName, SessionNo, LogonPENo\n",
    "FROM MONITORSESSIONSV(   \n",
    "USING HOSTID('-1')\n",
    "       USERNAME('*')\n",
    "       SESSIONID('0')\n",
    "       ) AS DT\n",
    "WHERE AmpState = 'ACTIVE'\n",
    "    AND HostID = 1\n",
    "ORDER BY 1,2;\n",
    "       '''\n",
    "display(pd.read_sql(qry, eng))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "collectible-infection",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#E37C4D'>Parent/Child Sessions, SQL Steps, SQL Text</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>MonitorSessionSV will return information about logged-in sessions for both the Parent/Primary and Child/Compute \"users\". This session information can be used to monitor active session SQL Steps and SQL Text using additional APIs.\n",
    "<br>\n",
    "<b>Note, there should be active Queries on the system for this section.  Ensure the Lab 1 workload is still running. If you see the message \"No Active Sessions to retrieve SQL\" rerun the workload generation.</b></p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial'>\n",
    "    <li>Get the current active session HostID, SessionNo, and VProc</li>\n",
    "    <li>Join Parent to Child Sessions</li>\n",
    "    <li>Take the first session, pass Session Number and VProc to MonitorSQLStepsSV</li>\n",
    "    <li>Take the same session, pass Session Number and VProc to \n",
    "    </ol>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "acoustic-radical",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "qry = '''\n",
    "/* First - get the current active session HostID, SessionNo, and VProc */\n",
    "\n",
    "SELECT HostId, SessionNo, RunVprocNo, Username from MonitorSessionSV(    \n",
    "  USING\n",
    "    hostId('-1')\n",
    "    userName('*')\n",
    "    sessionId('0')\n",
    "    details('1')\n",
    ") \n",
    "AS dt\n",
    "WHERE AmpState='ACTIVE';'''\n",
    "\n",
    "print('Active Sessions:')\n",
    "display(pd.read_sql(qry, eng))\n",
    "\n",
    "qry = '''\n",
    "\n",
    "/* Second, join Parent to Child Sessions */\n",
    "SELECT dt.HostId PrimaryHost, \n",
    "    dt.SessionNo PrimarySession, \n",
    "    dt.RunVProcNo PrimaryVProc, \n",
    "    dc.SessionNo ComputeSession, \n",
    "    dc.HostId ComputeHost, \n",
    "    dc.RunVProcNo ComputeVproc, \n",
    "    dt.UserName PrimaryUser, \n",
    "    dc.UserName ComputeUser\n",
    "FROM MonitorSessionSV(    \n",
    "  USING\n",
    "    hostId('-1')\n",
    "    userName('*')\n",
    "    sessionId('0')\n",
    "    details('1')\n",
    ") \n",
    "AS dt,\n",
    "MonitorSessionSV(    \n",
    "  USING\n",
    "    hostId('-1')\n",
    "    userName('*')\n",
    "    sessionId('0')\n",
    "    details('1')\n",
    ") AS dc\n",
    "WHERE dt.AmpState='ACTIVE' AND\n",
    "    dt.ParentSessionNo = 0 AND\n",
    "    dc.ParentSessionNo > 0 AND\n",
    "    dc.ParentSessionNo = dt.SessionNo;'''\n",
    "\n",
    "df = pd.read_sql(qry, eng)\n",
    "print('Parent/Child Sessions:')\n",
    "display(df)\n",
    "\n",
    "if len(df) > 0:\n",
    "    sess = df['PrimarySession'].iloc[0]\n",
    "    vproc = df['PrimaryVProc'].iloc[0]\n",
    "\n",
    "    qry = f'''\n",
    "    /* Pass these values into the function */\n",
    "    SELECT StepNum, EstRowCount, ActRowCount, SQLStep\n",
    "    FROM MonitorSQLStepsSV(\n",
    "      USING\n",
    "        HostIdIn('1')\n",
    "        SessionNoIn('{sess}')\n",
    "        RunVProcNo('{vproc}')\n",
    "        details('1')\n",
    "    ) AS dt;\n",
    "    '''\n",
    "    print('SQL Steps:')\n",
    "    df = pd.read_sql(qry, eng)\n",
    "    display(df)\n",
    "\n",
    "    qry = f'''\n",
    "    SELECT SessionNo, SQLTxt from MonitorSQLTextSV(\n",
    "      USING\n",
    "        HostIdIn('1')\n",
    "        SessionNoIn('{sess}')\n",
    "        RunVProcNo('{vproc}')\n",
    "        details('1')\n",
    "    ) AS dt;\n",
    "    '''\n",
    "    print('SQL Text:')\n",
    "    display(pd.read_sql(qry, eng))\n",
    "\n",
    "else:\n",
    "    print('No Active Sessions to retrieve SQL')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cutting-registration",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<p style = 'font-size:24px;font-family:Arial;color:#E37C4D'><b>Query History and Logging</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'><a href = 'https://docs.teradata.com/r/Teradata-VantageCloud-Lake/Monitoring-Performance-and-Resource-Usage/Performance-and-Query-Monitoring/Performance-Trends-and-History/Monitoring-Queries'>Database Query Logging (DBQL)</a> can be used to monitor prior queries and query performance.</p>\n",
    "\n",
    "<hr>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#E37C4D'>Query Logs - Compute Queries</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial'>Uses the td_metrics_svc.dbqlogv table to retrieve query logs for queries running on a Compute Cluster. This query assumes there is a parent-child query ID relationship as above with Sessions.\n",
    "<br><br>\n",
    "Next, we can join this to some of the system queries to assemble a more complete picture of Compute instances used.</p>\n",
    "\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "official-sherman",
   "metadata": {},
   "outputs": [],
   "source": [
    "qry = '''\n",
    "-- This query returns info on the session and instance used\n",
    "\n",
    "select TOP 10\n",
    "    a.StartTime, \n",
    "    a.QueryID, \n",
    "    a.SessionID, \n",
    "    a.path_component_id PrimaryClusterID, \n",
    "    b.path_component_id ComputeClusterID, \n",
    "    b.QueryID ComputeQueryID, \n",
    "    a.querytext, \n",
    "    b.AcctString  \n",
    "from td_metric_svc.dbqlogv a\n",
    "inner join td_metric_svc.dbqlogv b on a.QueryID = b.ParentQueryID\n",
    "order by a.StartTime DESC;'''\n",
    "\n",
    "display(pd.read_sql(qry, eng))\n",
    "\n",
    "qry = '''\n",
    "--find User queries executed on Compute Group\n",
    "\n",
    "SELECT TOP 10\n",
    "    a.queryid, \n",
    "    a.UserName, \n",
    "    a.StartTime, \n",
    "    a.sessionid, \n",
    "    a.ProcID VProcID, \n",
    "    rn.ProcID, \n",
    "    a.querytext, \n",
    "    rn.ComputeGroupName, \n",
    "    rn.ComputeProfileName, \n",
    "    rn.InstanceName\n",
    "\n",
    "/* First - get logged queries that have run on the Compute Group */\n",
    "FROM td_metric_svc.dbqlogv a\n",
    "inner join td_metric_svc.dbqlogv b \n",
    "    on a.QueryID = b.ParentQueryID\n",
    "\n",
    "/* next, join the COGID from query log to the compute group friendly name */\n",
    "JOIN (SELECT pr.\"Id\", pr.\"Group\", pr.\"ProcID\", g.ComputeGroupName, p.ComputeProfileName, s.InstanceName\n",
    "            FROM syslib.MonitorPhysicalResourceSV(USING details('1')) pr, DBC.ComputeStatusV s, DBC.ComputeProfilesV p, DBC.ComputeGroupsV g\n",
    "            WHERE pr.\"Id\" LIKE '%' || s.InstanceName || '%' AND\n",
    "                 g.ComputeGroupName=p.ComputeGroupName and\n",
    "                 p.ComputeProfileName=s.ComputeProfileName) rn\n",
    "            ON rn.ID = b.path_component_id\n",
    "\n",
    "GROUP BY 1,2,3,4,5,6,7,8,9,10\n",
    "order by a.StartTime DESC;'''\n",
    "\n",
    "display(pd.read_sql(qry, eng))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dominican-table",
   "metadata": {},
   "source": [
    "Copyright 2023, Teradata Corporation\n",
    "VantageCloud Lake Systems Monitoring Lab Notebook v1.0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "laden-aquarium",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "convenient-candy",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cubic-pottery",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  },
  "toc-showmarkdowntxt": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}